﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace QLNSWPF2.ToolSupport
{
    class DataConnection
    {
        public SqlConnection connection;
        public SqlCommand command;
        public bool flagConnection;
        public string errorMessage;
        #region Contractor
        public DataConnection()
        {
            connection = new SqlConnection("Data Source=VAN_HUU-PC;Initial Catalog=QLNS;Integrated Security=True");
            if (command == null)
            {
                command = connection.CreateCommand();
            }
        }

        public DataConnection(string ConnectionString)
        {
            connection = new SqlConnection(ConnectionString);
            command = connection.CreateCommand();
        }
        #endregion
        #region Procedure Connection
        public bool OpenConnection()
        {
            if (connection.State != System.Data.ConnectionState.Open)
            {
                connection.Open();
                flagConnection = true;
            }
            else flagConnection = false;
            return flagConnection;
        }

        public void Close()
        {
            command.Connection.Close();
            flagConnection = false;
        }

        public void DisposeAll()
        {
            if (connection.State == System.Data.ConnectionState.Open)
            {
                command.Dispose();
                connection.Close();
                connection.Dispose();
            }
        }
        #endregion
        #region Procedure Command
        public void SetCommand(string commandText, CommandType commandType)
        {
            if (command != null)
            {
                command.CommandType = commandType;
                command.CommandText = commandText;
            }
            else return;
        }
        #endregion
        #region Procedure Parameter
        public void AddParameter(string parameterName, object value)
        {
            SqlParameter parameter = new SqlParameter(parameterName, value);
            command.Parameters.Add(parameter);
        }
        public void AddParameter(string[] parameterName, object[] parameterValue)
        {
            for (int i = 0; i < parameterName.Length; i++)
            {
                SqlParameter parameter = new SqlParameter(parameterName[i], parameterValue[i]);
                command.Parameters.Add(parameter);
            }
        }
        public void AddParameter(string parameterName, object value, DbType type, ParameterDirection direction)
        {
            SqlParameter parameter = new SqlParameter(parameterName, value);
            parameter.Direction = direction;
            command.Parameters.Add(parameter);
        }
        #endregion
        #region Execute DataReader
        public SqlDataReader ExecuteDataReader()
        {
            SqlDataReader dataReader = null;
            try
            {
                dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (System.Exception ex)
            {
                errorMessage = ex.Message;
            }
            finally
            {
                command.Dispose();
            }
            return dataReader;
        }
        public SqlDataReader ExecuteDataReader(string commandText, CommandType type)
        {
            SqlDataReader dataReader = null;
            try
            {
                SetCommand(commandText, type);
                this.OpenConnection();
                dataReader = command.ExecuteReader();
            }
            catch (System.Exception ex)
            {
                errorMessage = ex.Message;
            }
            return dataReader;
        }
        public SqlDataReader ExecuteDataReader(string commandText, CommandType type, SqlParameterCollection parameters)
        {
            SqlDataReader dataReader = null;
            try
            {
                SetCommand(commandText, type);
                foreach (SqlParameter para in parameters)
                {
                    command.Parameters.Add(para);
                }
                this.OpenConnection();
                dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (System.Exception ex)
            {
                errorMessage = ex.Message;
            }
            return dataReader;
        }
        #endregion
        #region ExecuteNonQuery
        public int ExecuteNonQuery()
        {
            int i = 0;
            try
            {
                this.OpenConnection();
                i = command.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                errorMessage = ex.Message;
            }
            return i;
        }
        public int ExecuteNonQuery(string commandText, CommandType commandType)
        {
            int i = 0;
            try
            {
                SetCommand(commandText, commandType);
                this.OpenConnection();
                i = command.ExecuteNonQuery();
                this.Close();
            }
            catch (System.Exception ex)
            {
                errorMessage = ex.Message;
            }
            return i;
        }
        public int ExecuteNonQuery(string commandText, CommandType type, SqlParameterCollection parameters)
        {
            int i = 0;
            try
            {
                this.OpenConnection();
                SetCommand(commandText, type);
                command.Parameters.Clear();
                foreach (SqlParameter para in parameters)
                {
                    command.Parameters.Add(para);
                }
                i = command.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                errorMessage = ex.Message;
            }
            finally
            {
                command.Dispose();
            }
            return i;
        }
        #endregion
        #region ExecuteScalar
        public object ExecuteScalar()
        {
            object row = null;
            try
            {
                this.OpenConnection();
                row = command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                errorMessage = ex.Message;
            }
            finally
            {
                command.Dispose();
            }
            return row;
        }

        public object ExecuteScalar(string commandText, CommandType type)
        {
            object row = null;
            try
            {
                this.OpenConnection();
                this.SetCommand(commandText, type);
                row = command.ExecuteScalar();
                this.Close();
            }
            catch (Exception ex)
            {
                errorMessage = ex.Message;
            }
            finally
            {
                command.Dispose();
            }
            return row;
        }

        public object ExecuteScalar(string commandText, CommandType type, SqlParameterCollection parameters)
        {
            object row = null;
            try
            {
                this.OpenConnection();
                SetCommand(commandText, type);
                command.Parameters.Clear();
                foreach (SqlParameter para in parameters)
                {
                    command.Parameters.Add(para);
                }
                row = command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                errorMessage = ex.Message;
            }
            finally
            {
                command.Dispose();
            }
            return row;
        }

        #endregion
        #region Use DataAdapter
        public DataSet ExecuteDatSet()
        {
            SqlDataAdapter iDDA = null;
            DataSet ds = new DataSet();
            try
            {
                iDDA = new SqlDataAdapter(command);
                iDDA.Fill(ds);
            }
            catch (Exception ex)
            {
                errorMessage = ex.Message;
            }
            finally
            {
                command.Dispose();
            }
            return ds;
        }

        //Điền dữ liệu vào DataSet
        public int FillDataSet(DataSet dataSet)
        {
            int result = 0;
            try
            {
                this.OpenConnection();
                SqlDataAdapter iDA = new SqlDataAdapter(command);
                result = iDA.Fill(dataSet);
            }
            catch (Exception ex)
            {
                errorMessage = ex.Message;
            }
            finally
            {
                command.Dispose();
            }
            return result;
        }

        //Thực thi câu lệnh bằng đối tượng DataSet với câu lệnh SQL và kiểu câu lệnh.
        public DataSet ExecuteDatSet(string commandText, CommandType type)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter iDA = null;
            try
            {
                SetCommand(commandText, type);
                iDA = new SqlDataAdapter(command);
                iDA.Fill(ds);
            }
            catch (Exception ex)
            {
                errorMessage = ex.Message;
            }
            finally
            {
                command.Dispose();
            }
            return ds;
        }

        //Thực thi câu lệnh bằng đối tượng DataSet với câu lệnh SQL, kiểu câu lệnh và tham số
        public DataSet ExecuteDatSet(string commandText, CommandType type, SqlParameterCollection parameters)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter iDDA = null;
            try
            {
                SetCommand(commandText, type);
                command.Parameters.Clear();
                foreach (SqlParameter para in parameters)
                {
                    command.Parameters.Add(para);
                }
                iDDA.Fill(ds);
            }
            catch (Exception ex)
            {
                errorMessage = ex.Message;
            }
            finally
            {
                command.Dispose();
            }
            return ds;
        }
        //lấy dữ liệu retun ra table
        public DataTable ExecuteDataTableAll(string commandText, CommandType type)
        {
            DataTable dataTable = new DataTable();
            try
            {
                SetCommand(commandText, type);
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(dataTable);
            }
            catch (System.Exception ex)
            {
                errorMessage = ex.Message;
            }
            finally
            {
                command.Dispose();
            }
            return dataTable;
        }
        public DataTable ExecuteDataTableLimit(string commandText, CommandType type, int startRecord, int numberRecord)
        {
            DataTable dataTable = new DataTable();
            try
            {
                SetCommand(commandText, type);
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(startRecord, numberRecord, dataTable);
            }
            catch (System.Exception ex)
            {
                errorMessage = ex.Message;
            }
            finally
            {
                command.Dispose();
            }
            return dataTable;
        }
        #endregion
        #region Other
        public int GetValidReturnOfProcedure(string stringProcedure, ParameterDirection direction)
        {
            int i = 0;
            try
            {
                this.OpenConnection();
                SqlParameter parameter = new SqlParameter("@resultOutput", SqlDbType.Int);
                parameter.Direction = direction;
                command.Parameters.Add(parameter);
                SetCommand(stringProcedure, CommandType.StoredProcedure);
                command.ExecuteNonQuery();
                i = int.Parse(parameter.Value.ToString());
                this.Close();
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return i;
        }
        public object[] GetObject(string parameterName, object parameterValue)
        {
            object[] objItem = null;
            try
            {
                command.Parameters.Clear();
                AddParameter(parameterName, parameterValue);
                SqlDataReader reader = ExecuteDataReader("udsGetAutoId", System.Data.CommandType.StoredProcedure);
                int i = reader.FieldCount;
                if (reader.Read())
                {
                    objItem = new object[i];
                    reader.GetValues(objItem);
                }
                this.Close();
                reader.Dispose();
            }
            catch (System.Exception ex)
            {
                errorMessage = ex.Message;
            }
            return objItem;
        }

        public void BeginTransaction()
        {
            if (this.InTransaction)
            {
                return;
            }
            if (command.Connection.State != ConnectionState.Open)
            {
                command.Connection.Open();
            }
            SqlTransaction transaction = command.Connection.BeginTransaction();
            command.Transaction = transaction;
            InTransaction = true;
        }

        //Chuyển tác có tham số.
        public void BeginTransaction(IsolationLevel lv)
        {
            SqlTransaction transaction = command.Connection.BeginTransaction(lv);
            command.Transaction = transaction;
            InTransaction = true;
        }
        //Thực hiện Chuyển tác
        public void Comit()
        {
            if (InTransaction)
            {
                command.Transaction.Commit();
            }
            InTransaction = false;
        }

        //Thực thi chuyển tác không thành công trở về ban đầu.
        public void RollBack()
        {
            if (InTransaction)
            {
                try
                {
                    command.Transaction.Rollback();
                }
                catch (InvalidOperationException ex)
                {
                    if (HandleErrors)
                    {
                        StringError.Append("Error : RollBack ");
                        StringError.Append(ex.Message);
                        StringError.Append("\n");
                    };
                    this.Close();
                }
                finally
                {
                    InTransaction = false;
                }
            }
        }
        //Lỗi trả về
        public string GetError()
        {
            return strErr.ToString();
        }
        //Dọn dẹp lỗi và trả về lỗi.
        public string ClearError()
        {
            string str = strErr.ToString();
            strErr = new StringBuilder();
            return str;
        }

        private bool handleErrors;
        protected bool HandleErrors
        {
            get { return handleErrors; }
            set { handleErrors = value; }
        }
        private StringBuilder strErr = new StringBuilder();
        protected StringBuilder StringError
        {
            get { return strErr; }
            set { strErr = value; }
        }
        //Kiểm tra chuyển tác.
        private bool inTran = false;
        protected bool InTransaction
        {
            set { inTran = value; }
            get { return inTran; }
        }
        #endregion
    }
}
